"""
cron: 0 */4 * * *
new Env('招聘信息分析专业版');
"""
import requests
import os
import mysql.connector
import json
import time
from datetime import datetime
from bs4 import BeautifulSoup

class JobAnalysisPro:
    def __init__(self):
        self.connection = mysql.connector.connect(
            host=os.environ.get('HOST'),
            user=os.environ.get('USER'),
            password=os.environ.get('PASSWORD'),
            database=os.environ.get('DATABASE')
        )
        self.cursor = self.connection.cursor()
        self.create_tables()
        
    def create_tables(self):
        """创建必要的数据表"""
        # 职位信息表
        self.cursor.execute("""
            CREATE TABLE IF NOT EXISTS ningm_job_info (
                id INT AUTO_INCREMENT PRIMARY KEY,
                job_id VARCHAR(50),
                title VARCHAR(200),
                company VARCHAR(200),
                city VARCHAR(50),
                salary_min INT,
                salary_max INT,
                experience VARCHAR(50),
                education VARCHAR(50),
                skills TEXT,
                welfare TEXT,
                create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_city (city),
                INDEX idx_create_time (create_time)
            )
        """)
        
        # 薪资分析表
        self.cursor.execute("""
            CREATE TABLE IF NOT EXISTS ningm_salary_analysis (
                id INT AUTO_INCREMENT PRIMARY KEY,
                city VARCHAR(50),
                job_type VARCHAR(100),
                avg_salary DECIMAL(10,2),
                min_salary DECIMAL(10,2),
                max_salary DECIMAL(10,2),
                salary_distribution TEXT,
                sample_count INT,
                create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_city (city),
                INDEX idx_job_type (job_type)
            )
        """)
        
        # 技能需求表
        self.cursor.execute("""
            CREATE TABLE IF NOT EXISTS ningm_skill_demand (
                id INT AUTO_INCREMENT PRIMARY KEY,
                skill_name VARCHAR(100),
                demand_count INT,
                avg_salary DECIMAL(10,2),
                job_types TEXT,
                hot_companies TEXT,
                growth_rate DECIMAL(5,2),
                create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_skill_name (skill_name),
                INDEX idx_demand_count (demand_count)
            )
        """)
        
        # 公司分析表
        self.cursor.execute("""
            CREATE TABLE IF NOT EXISTS ningm_company_analysis (
                id INT AUTO_INCREMENT PRIMARY KEY,
                company_name VARCHAR(200),
                job_count INT,
                avg_salary DECIMAL(10,2),
                main_cities TEXT,
                hot_positions TEXT,
                welfare_tags TEXT,
                company_score DECIMAL(3,1),
                create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_company_name (company_name),
                INDEX idx_company_score (company_score)
            )
        """)

    def get_zhipin_jobs(self, city, job_type):
        """获取BOSS直聘职位数据"""
        try:
            headers = {
                'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36',
                'Cookie': os.environ.get('ZHIPIN_COOKIE', '')
            }
            
            url = f'https://www.zhipin.com/c{city}/?query={job_type}'
            response = requests.get(url, headers=headers)
            
            if response.status_code == 200:
                soup = BeautifulSoup(response.text, 'html.parser')
                job_list = soup.find_all('div', class_='job-primary')
                
                for job in job_list:
                    # 提取职位信息
                    title = job.find('div', class_='job-title').text.strip()
                    company = job.find('div', class_='company-text').find('h3').text.strip()
                    salary = job.find('span', class_='red').text.strip()
                    
                    # 处理薪资范围
                    salary_range = salary.replace('k', '000').split('-')
                    salary_min = int(salary_range[0])
                    salary_max = int(salary_range[1]) if len(salary_range) > 1 else salary_min
                    
                    # 提取其他信息
                    info_list = job.find_all('p')
                    city = info_list[0].text.split('·')[0].strip()
                    experience = info_list[0].text.split('·')[1].strip()
                    education = info_list[0].text.split('·')[2].strip()
                    
                    # 获取技能标签
                    skills = [tag.text for tag in job.find_all('span', class_='tag')]
                    
                    # 获取福利标签
                    welfare = [tag.text for tag in job.find_all('div', class_='info-desc')]
                    
                    job_data = {
                        'job_id': job['data-jid'],
                        'title': title,
                        'company': company,
                        'city': city,
                        'salary_min': salary_min,
                        'salary_max': salary_max,
                        'experience': experience,
                        'education': education,
                        'skills': json.dumps(skills, ensure_ascii=False),
                        'welfare': json.dumps(welfare, ensure_ascii=False)
                    }
                    
                    self.save_job_data(job_data)
                    
        except Exception as e:
            print(f"获取BOSS直聘数据失败: {str(e)}")

    def analyze_salary(self, city=None, job_type=None):
        """分析薪资情况"""
        try:
            # 构建查询条件
            conditions = []
            params = []
            
            if city:
                conditions.append("city = %s")
                params.append(city)
            if job_type:
                conditions.append("title LIKE %s")
                params.append(f"%{job_type}%")
                
            where_clause = " AND ".join(conditions) if conditions else "1=1"
            
            # 获取薪资数据
            query = f"""
                SELECT 
                    AVG((salary_min + salary_max) / 2) as avg_salary,
                    MIN(salary_min) as min_salary,
                    MAX(salary_max) as max_salary,
                    COUNT(*) as sample_count,
                    JSON_ARRAYAGG(
                        JSON_OBJECT(
                            'range', 
                            CASE 
                                WHEN salary_max <= 10000 THEN '0-10k'
                                WHEN salary_max <= 20000 THEN '10k-20k'
                                WHEN salary_max <= 30000 THEN '20k-30k'
                                WHEN salary_max <= 50000 THEN '30k-50k'
                                ELSE '50k+'
                            END,
                            'count', COUNT(*)
                        )
                    ) as distribution
                FROM ningm_job_info
                WHERE {where_clause}
                GROUP BY 
                    CASE 
                        WHEN salary_max <= 10000 THEN 1
                        WHEN salary_max <= 20000 THEN 2
                        WHEN salary_max <= 30000 THEN 3
                        WHEN salary_max <= 50000 THEN 4
                        ELSE 5
                    END
            """
            
            self.cursor.execute(query, params)
            result = self.cursor.fetchone()
            
            if result:
                insert_query = """
                    INSERT INTO ningm_salary_analysis 
                    (city, job_type, avg_salary, min_salary, max_salary, 
                     salary_distribution, sample_count) 
                    VALUES (%s, %s, %s, %s, %s, %s, %s)
                """
                insert_values = (
                    city,
                    job_type,
                    result[0],  # avg_salary
                    result[1],  # min_salary
                    result[2],  # max_salary
                    result[3],  # distribution
                    result[4]   # sample_count
                )
                self.cursor.execute(insert_query, insert_values)
                self.connection.commit()
                
        except Exception as e:
            print(f"分析薪资数据失败: {str(e)}")
            self.connection.rollback()

    def analyze_skills(self):
        """分析技能需求"""
        try:
            # 获取所有技能
            query = """
                SELECT 
                    JSON_UNQUOTE(JSON_EXTRACT(skills, '$[*]')) as skill,
                    COUNT(*) as demand_count,
                    AVG((salary_min + salary_max) / 2) as avg_salary,
                    GROUP_CONCAT(DISTINCT title) as job_types,
                    GROUP_CONCAT(DISTINCT company) as companies
                FROM ningm_job_info
                GROUP BY skill
                HAVING demand_count >= 5
                ORDER BY demand_count DESC
            """
            self.cursor.execute(query)
            results = self.cursor.fetchall()
            
            for result in results:
                skill, demand_count, avg_salary, job_types, companies = result
                
                # 计算增长率（与上周相比）
                growth_query = """
                    SELECT COUNT(*)
                    FROM ningm_job_info
                    WHERE JSON_CONTAINS(skills, %s)
                    AND create_time < DATE_SUB(NOW(), INTERVAL 7 DAY)
                """
                self.cursor.execute(growth_query, (skill,))
                last_week_count = self.cursor.fetchone()[0]
                
                growth_rate = ((demand_count - last_week_count) / last_week_count * 100) if last_week_count > 0 else 0
                
                # 保存分析结果
                insert_query = """
                    INSERT INTO ningm_skill_demand 
                    (skill_name, demand_count, avg_salary, job_types, 
                     hot_companies, growth_rate) 
                    VALUES (%s, %s, %s, %s, %s, %s)
                """
                insert_values = (
                    skill,
                    demand_count,
                    avg_salary,
                    job_types[:500],  # 限制长度
                    companies[:500],   # 限制长度
                    growth_rate
                )
                self.cursor.execute(insert_query, insert_values)
                
            self.connection.commit()
            
        except Exception as e:
            print(f"分析技能需求失败: {str(e)}")
            self.connection.rollback()

    def analyze_companies(self):
        """分析公司情况"""
        try:
            query = """
                SELECT 
                    company,
                    COUNT(*) as job_count,
                    AVG((salary_min + salary_max) / 2) as avg_salary,
                    GROUP_CONCAT(DISTINCT city) as cities,
                    GROUP_CONCAT(DISTINCT title) as positions,
                    GROUP_CONCAT(DISTINCT welfare) as welfare_tags
                FROM ningm_job_info
                GROUP BY company
                HAVING job_count >= 3
            """
            self.cursor.execute(query)
            results = self.cursor.fetchall()
            
            for result in results:
                company, job_count, avg_salary, cities, positions, welfare = result
                
                # 计算公司评分（1-5分）
                score = self.calculate_company_score(
                    job_count,
                    avg_salary,
                    len(cities.split(',')),
                    welfare
                )
                
                # 保存分析结果
                insert_query = """
                    INSERT INTO ningm_company_analysis 
                    (company_name, job_count, avg_salary, main_cities, 
                     hot_positions, welfare_tags, company_score) 
                    VALUES (%s, %s, %s, %s, %s, %s, %s)
                """
                insert_values = (
                    company,
                    job_count,
                    avg_salary,
                    cities,
                    positions[:500],  # 限制长度
                    welfare[:500],    # 限制长度
                    score
                )
                self.cursor.execute(insert_query, insert_values)
                
            self.connection.commit()
            
        except Exception as e:
            print(f"分析公司数据失败: {str(e)}")
            self.connection.rollback()

    def calculate_company_score(self, job_count, avg_salary, city_count, welfare):
        """计算公司评分"""
        score = 0
        
        # 职位数量评分（最高1分）
        if job_count >= 20:
            score += 1
        elif job_count >= 10:
            score += 0.8
        elif job_count >= 5:
            score += 0.5
        else:
            score += 0.3
            
        # 薪资水平评分（最高2分）
        if avg_salary >= 30000:
            score += 2
        elif avg_salary >= 20000:
            score += 1.5
        elif avg_salary >= 10000:
            score += 1
        else:
            score += 0.5
            
        # 城市覆盖评分（最高1分）
        if city_count >= 10:
            score += 1
        elif city_count >= 5:
            score += 0.7
        elif city_count >= 3:
            score += 0.4
        else:
            score += 0.2
            
        # 福利评分（最高1分）
        welfare_count = len(json.loads(welfare)) if welfare else 0
        if welfare_count >= 5:
            score += 1
        elif welfare_count >= 3:
            score += 0.6
        elif welfare_count >= 1:
            score += 0.3
            
        return min(score, 5)  # 最高5分

    def save_job_data(self, job_data):
        """保存职位数据"""
        try:
            insert_query = """
                INSERT INTO ningm_job_info 
                (job_id, title, company, city, salary_min, salary_max, 
                 experience, education, skills, welfare) 
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """
            insert_values = (
                job_data['job_id'],
                job_data['title'],
                job_data['company'],
                job_data['city'],
                job_data['salary_min'],
                job_data['salary_max'],
                job_data['experience'],
                job_data['education'],
                job_data['skills'],
                job_data['welfare']
            )
            self.cursor.execute(insert_query, insert_values)
            self.connection.commit()
            
        except Exception as e:
            print(f"保存职位数据失败: {str(e)}")
            self.connection.rollback()

    def run(self):
        """运行分析系统"""
        try:
            # 热门城市和职位类型
            cities = ['101010100', '101020100', '101280100', '101280600']  # 北上广深
            job_types = ['后端开发', '前端开发', '产品经理', 'UI设计师', '运营']
            
            # 1. 获取职位数据
            for city in cities:
                for job_type in job_types:
                    self.get_zhipin_jobs(city, job_type)
                    time.sleep(5)  # 避免请求过快
            
            # 2. 分析薪资情况
            for city in cities:
                for job_type in job_types:
                    self.analyze_salary(city, job_type)
            
            # 3. 分析技能需求
            self.analyze_skills()
            
            # 4. 分析公司情况
            self.analyze_companies()
            
        except Exception as e:
            print(f"系统运行失败: {str(e)}")
        finally:
            self.cursor.close()
            self.connection.close()

if __name__ == "__main__":
    analyzer = JobAnalysisPro()
    analyzer.run() 